﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Configuration;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;

/// <summary>
///MySqlHelper 的摘要说明
/// </summary>
public class MySqlHelper
{ 
    private MySqlConnection conn;

    private string connection = ConfigurationManager.ConnectionStrings["mysqlConnectionString"].ToString();
    public MySqlHelper()
    {
        Conn = new MySqlConnection(connection);
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }

    public int ExecuteSql(string cmdText)
    {
        MySqlCommand cmd = new MySqlCommand();
        using (MySqlConnection conn = new MySqlConnection(connection))
        {
            PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, null);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
    }

    public MySqlDataReader ExecuteReader(string cmdText)
    {
        MySqlCommand cmd = new MySqlCommand();
        MySqlConnection conn = new MySqlConnection(connection);
        try
        {
            PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, null);
            MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return reader;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }

    public DataSet ExecuteDs(string cmdText)
    {
        MySqlCommand cmd = new MySqlCommand();
        MySqlConnection conn = new MySqlConnection(connection);
        try
        {
            PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, null);
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            cmd.Parameters.Clear();
            conn.Close();
            return ds;
        }
        catch (Exception e)
        {
            throw e;
        }
    }

    public DataTable ExecuteDt(string SQLString)
    {
        using (MySqlConnection conn = new MySqlConnection(connection))
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                MySqlDataAdapter command = new MySqlDataAdapter(SQLString, conn);
                command.Fill(ds, "ds");
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds.Tables[0];
        }
    }


    //返回一个值
    public string GetString(string Sqlstr)
    {
        DataTable dt = ExecuteDt(Sqlstr);
        if (dt != null && dt.Rows.Count > 0)
        {
            return dt.Rows[0][0].ToString();
        }
        return "";
    } 

    /// <summary>
    /// 准备执行一个命令
    /// </summary>
    /// <param name="cmd">sql命令</param>
    /// <param name="conn">OleDb连接</param>
    /// <param name="trans">OleDb事务</param>
    /// <param name="cmdType">命令类型例如 存储过程或者文本</param>
    /// <param name="cmdText">命令文本,例如:Select * from Products</param>
    /// <param name="cmdParms">执行命令的参数</param>
    private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
    {

        if (conn.State != ConnectionState.Open)
            conn.Open();

        cmd.Connection = conn;
        cmd.CommandText = cmdText;

        if (trans != null)
            cmd.Transaction = trans;

        cmd.CommandType = cmdType;

        if (cmdParms != null)
        {
            foreach (MySqlParameter parm in cmdParms)
                cmd.Parameters.Add(parm);
        }
    }

    public MySqlConnection Conn
    {
        get
        {
            return conn;
        }

        set
        {
            conn = value;
        }
    }

}